﻿using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Tools
{
    public class Read_excel
    {
        public static DataTable read_excel(string FileName)
        {
            System.Data.DataTable dt = null;
            try
            {
                string strConn = $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={FileName};Extended Properties=Excel 8.0;";

                using (OleDbConnection conn = new OleDbConnection(strConn))
                {
                    conn.Open();
                    System.Data.DataTable sheetDt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    string[] sheet = new string[sheetDt.Rows.Count];
                    for (int i = 0; i < sheetDt.Rows.Count; i++)
                    {
                        sheet[i] = sheetDt.Rows[i]["TABLE_NAME"].ToString();
                    }
                    string strExcel = string.Format("select * from [{0}]", sheet[0]);
                    OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn);
                    dt = new System.Data.DataTable();
                    myCommand.Fill(dt);
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                return null;
            }
            return dt;
        }
    }
}
